package com.example.test_witns.utils;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MyInfosHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "myInfo.db";
    private static final int DATABASE_VERSION = 1;
    private static final String COLUMN_ID = "id";
    private static final String TABLE_NAME = "myInfoTable3";
    private static final String COLUMN_EMAIL = "email";
    private static final String COLUMN_SIG = "sig";
    private static final String COLUMN_NAME = "name";
    private static final String COLUMN_AVATAR_BYTES = "avatar";
    private static final String COLUMN_AVATAR_URI = "avatar_uri";
    private static final String COLUMN_AVATAR_URL = "avatar_url";
    private static final String COLUMN_UUID = "uuid";

    public interface GetMyInfos {
        void onLoadingMyInfos(String email ,String sig, String name, String uuid, byte[] avatar, String uri, String url);
    }

    private static final String my_Info_TABLE_CREATE =
            "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_EMAIL + " TEXT, " +
                    COLUMN_NAME + " TEXT, " +
                    COLUMN_AVATAR_URI + " TEXT, " +
                    COLUMN_AVATAR_URL + " TEXT, " +
                    COLUMN_AVATAR_BYTES + " BLOB, " +
                    COLUMN_SIG + " TEXT, " +
                    COLUMN_UUID + " TEXT" +
                    ");";

    public boolean dropAndCreateNewTable(){
        deleteMyInfosTable();
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(my_Info_TABLE_CREATE);
        return true;
    }

    public boolean checkIfLogin(){
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL(my_Info_TABLE_CREATE);
        String countQuery = "SELECT COUNT(*) FROM " + TABLE_NAME;
        Cursor cursor = db.rawQuery(countQuery, null);
        boolean isLogin = false;

        if (cursor.moveToFirst()){
            int count = cursor.getInt(0);
            isLogin = !(count == 0);
        }
        cursor.close();
        db.close();
        return isLogin;
    }

    public long addMyInfos(String email ,String username, String sig, String uuid, byte[] avatar, String avatarURI, String avatarURL){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        db.execSQL(my_Info_TABLE_CREATE);
        values.put(MyInfosHelper.COLUMN_AVATAR_BYTES, avatar);
        values.put(MyInfosHelper.COLUMN_SIG, sig);
        values.put(MyInfosHelper.COLUMN_NAME, username);
        values.put(MyInfosHelper.COLUMN_UUID, uuid);
        values.put(MyInfosHelper.COLUMN_EMAIL, email);
        values.put(MyInfosHelper.COLUMN_AVATAR_URI, avatarURI);
        values.put(MyInfosHelper.COLUMN_AVATAR_URL, avatarURL);
        long newRowId = db.insert(MyInfosHelper.TABLE_NAME, null, values);
        db.close();

        return newRowId;
    }

    public void loadMyInfos(GetMyInfos getMyInfos){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);
        while (cursor.moveToNext()){
            @SuppressLint("Range") String email = cursor.getString(cursor.getColumnIndex(COLUMN_EMAIL));
            @SuppressLint("Range") String name = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
            @SuppressLint("Range") byte[] avatar = cursor.getBlob(cursor.getColumnIndex(COLUMN_AVATAR_BYTES));
            @SuppressLint("Range") String sig = cursor.getString(cursor.getColumnIndex(COLUMN_SIG));
            @SuppressLint("Range") String uuid = cursor.getString(cursor.getColumnIndex(COLUMN_UUID));
            @SuppressLint("Range") String uri = cursor.getString(cursor.getColumnIndex(COLUMN_AVATAR_URI));
            @SuppressLint("Range") String url = cursor.getString(cursor.getColumnIndex(COLUMN_AVATAR_URL));

            getMyInfos.onLoadingMyInfos(email ,sig, name, uuid, avatar, uri, url);
        }
    }

    public MyInfosHelper(Context context){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public void deleteMyInfosTable(){
        SQLiteDatabase db = this.getWritableDatabase();
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
    }

    @SuppressLint("Range")
    public String getMyUserId(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String email = null;
        while (cursor.moveToNext()){
            email = cursor.getString(cursor.getColumnIndex(COLUMN_EMAIL));
        }
        return email;
    }

    @SuppressLint("Range")
    public String getMyNickname(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String nickname = null;
        while (cursor.moveToNext()){
            nickname = cursor.getString(cursor.getColumnIndex(COLUMN_NAME));
        }
        return nickname;
    }

    public boolean updateAvatarURI(String URI){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String email = null;
        cursor.moveToLast();
        long id = cursor.getLong(cursor.getColumnIndexOrThrow("id"));
        ContentValues values = new ContentValues();
        values.put(COLUMN_AVATAR_URI, URI);

        int rowsUpdated = db.update(TABLE_NAME, values, "id=?", new String[]{String.valueOf(id)});
        if (rowsUpdated > 0) {
            return true;
        } else {
            return false;
        }
    }

    @SuppressLint("Range")
    public String getAvatarURI(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String uri = null;
        cursor.moveToLast();
        uri = cursor.getString(cursor.getColumnIndex(COLUMN_AVATAR_URI));
        return uri;
    }

    public boolean updateMyAvatarURL(String myEmail, String newAvatarURL){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String email = null;
        cursor.moveToFirst();
        long id = cursor.getLong(cursor.getColumnIndexOrThrow("id"));
        ContentValues values = new ContentValues();
        values.put("avatar_url", newAvatarURL);

        int rowsUpdated = db.update(TABLE_NAME, values, "id=?", new String[]{String.valueOf(id)});
        if (rowsUpdated > 0) {
            return true;
        } else {
            return false;
        }
    }

    @SuppressLint("Range")
    public String getMyAvatarURL(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String uri = null;
        cursor.moveToLast();
        uri = cursor.getString(cursor.getColumnIndex(COLUMN_AVATAR_URL));
        return uri;
    }

    @SuppressLint("Range")
    public String getMyUUID(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String uri = null;
        cursor.moveToLast();
        uri = cursor.getString(cursor.getColumnIndex(COLUMN_UUID));
        return uri;
    }

    @SuppressLint("Range")
    public boolean updateAvatarByName(String name, byte[] avatarBytes) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        String email = null;
        cursor.moveToFirst();
        long id = cursor.getLong(cursor.getColumnIndexOrThrow("id"));
        ContentValues values = new ContentValues();
        values.put("avatar", avatarBytes);

        int rowsUpdated = db.update(TABLE_NAME, values, "id=?", new String[]{String.valueOf(id)});
        if (rowsUpdated > 0) {
            return true;
        } else {
            return false;
        }
    }

    @SuppressLint("Range")
    public byte[] getAvatarByName(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null, null);

        byte[] avatar = null;
        cursor.moveToLast();
        avatar = cursor.getBlob(cursor.getColumnIndex(COLUMN_AVATAR_BYTES));
        return avatar;
    }

    public long getMaxIdByName(String name) {
        SQLiteDatabase db = this.getReadableDatabase();
        long maxId = -1; // 假设-1为无效的ID值
        Cursor cursor = db.rawQuery("SELECT MIN(id) FROM myInfoTable2 WHERE email=?", new String[]{name});

        if (cursor.moveToFirst()) {
            maxId = cursor.getLong(0); // 获取最大ID值
        }
        return maxId;
    }

    /**
     * @param db The database.
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(my_Info_TABLE_CREATE);
    }

    /**
     * @param db         The database.
     * @param oldVersion The old database version.
     * @param newVersion The new database version.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
